﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace AbhiRuchiFoods
{
    class AbhiruchiDAL
    {
        //static string connection = "Data Source=118.139.163.181;Initial Catalog=Abhiruchi;User ID=sa;Password=Sairam123%";
        static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AbhiRuchiConnectionString"].ConnectionString);
        static SqlCommand cmd;
        static SqlDataAdapter da;
        static DataTable dt;

        public static int GetEmployeeNoSeq(string EmpType)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetEmployeeNoBasedOnEmpType";
            cmd.Parameters.AddWithValue("@EmpType", EmpType);
            cmd.CommandType = CommandType.StoredProcedure;
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            int no=Convert.ToInt32(dt.Rows[0][0]); 
            return no;
        }

        public static void resetSeed()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@TransType", 6);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void setEmployeeBiometricInformation(string id, string fingerId, string sampleNo,byte[] fingerPrint,string fingerText)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployeeBiometricInfo";
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@FingerId", fingerId);
            cmd.Parameters.AddWithValue("@SampleNo", sampleNo);
            cmd.Parameters.AddWithValue("@FingerPrint", fingerPrint);
            cmd.Parameters.AddWithValue("@FingerPrintText", fingerText);
            cmd.Parameters.AddWithValue("@TransType", 1);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void ModifyEmployeeBiometricInformation(string id, string fingerId, string sampleNo, byte[] fingerPrint, string fingerText)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployeeBiometricInfo";
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@FingerId", fingerId);
            cmd.Parameters.AddWithValue("@SampleNo", sampleNo);
            cmd.Parameters.AddWithValue("@FingerPrint", fingerPrint);
            cmd.Parameters.AddWithValue("@FingerPrintText", fingerText);
            cmd.Parameters.AddWithValue("@TransType", 3);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static DataTable getEmployeeBiometricInfo()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployeeBiometricInfo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TransType", 2);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmpBio = new DataTable();
            da.Fill(dtEmpBio);
            return dtEmpBio;
        }

        public static void SetPermanentEmployeeInformation(string fname,string lname,string faName,string dob,string qualification,
        string referedBy,string phoneNo,string personalEmail,string companyEmail,string address,int active,int permanent,string photo)
        {
            int No=GetEmployeeNoSeq("PE");
            No++;
            string EmpNo="PE"+(No).ToString();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@EmployeeNo", EmpNo);
            cmd.Parameters.AddWithValue("@EmployeeName", fname + " " + lname);
            cmd.Parameters.AddWithValue("@FatherName", faName);
            cmd.Parameters.AddWithValue("@DOB", dob.Split('/').GetValue(1) + "/" + dob.Split('/').GetValue(0) + "/" + dob.Split('/').GetValue(2));
            cmd.Parameters.AddWithValue("@Qualification", qualification);
            cmd.Parameters.AddWithValue("@ReferredBy", referedBy);
            cmd.Parameters.AddWithValue("@PhoneNo", phoneNo);
            cmd.Parameters.AddWithValue("@PersonalEmail", personalEmail);
            cmd.Parameters.AddWithValue("@CompanyEmail", companyEmail);
            cmd.Parameters.AddWithValue("@Address", address);
            cmd.Parameters.AddWithValue("@IsActive", active);
            cmd.Parameters.AddWithValue("@IsPermanent", permanent);
            cmd.Parameters.AddWithValue("@Photo", photo);
            cmd.Parameters.AddWithValue("@TransType", 1);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void SetTempEmployeeInformation(string fname, string lname, string faName, string dob, string qualification,
        string referedBy, string phoneNo, string personalEmail, string companyEmail, string address, int active, int permanent,string photo)
        {
            int No = GetEmployeeNoSeq("TE");
            No++;
            string EmpNo = "TE" + (No).ToString();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@EmployeeNo", EmpNo);
            cmd.Parameters.AddWithValue("@EmployeeName", fname+" "+lname);
            cmd.Parameters.AddWithValue("@FatherName", faName);
            cmd.Parameters.AddWithValue("@DOB", dob);
            cmd.Parameters.AddWithValue("@Qualification", qualification);
            cmd.Parameters.AddWithValue("@ReferredBy", referedBy);
            cmd.Parameters.AddWithValue("@PhoneNo", phoneNo);
            cmd.Parameters.AddWithValue("@PersonalEmail", personalEmail);
            cmd.Parameters.AddWithValue("@CompanyEmail", companyEmail);
            cmd.Parameters.AddWithValue("@Address", address);
            cmd.Parameters.AddWithValue("@IsActive", active);
            cmd.Parameters.AddWithValue("@IsPermanent", permanent);
            cmd.Parameters.AddWithValue("@Photo", photo);
            cmd.Parameters.AddWithValue("@TransType", 1);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }


        public static void ModifyPermanentEmployeeInformation(string EmpNo,string fname, string lname, string faName, string dob, string qualification,
        string referedBy, string phoneNo, string personalEmail, string companyEmail, string address, int active, int permanent,string photo)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@EmployeeNo", EmpNo);
            cmd.Parameters.AddWithValue("@EmployeeName", fname + " " + lname);
            cmd.Parameters.AddWithValue("@FatherName", faName);
            cmd.Parameters.AddWithValue("@DOB", dob.Split('/').GetValue(1) + "/" + dob.Split('/').GetValue(0) + "/" + dob.Split('/').GetValue(2));
            cmd.Parameters.AddWithValue("@Qualification", qualification);
            cmd.Parameters.AddWithValue("@ReferredBy", referedBy);
            cmd.Parameters.AddWithValue("@PhoneNo", phoneNo);
            cmd.Parameters.AddWithValue("@PersonalEmail", personalEmail);
            cmd.Parameters.AddWithValue("@CompanyEmail", companyEmail);
            cmd.Parameters.AddWithValue("@Address", address);
            cmd.Parameters.AddWithValue("@IsActive", active);
            cmd.Parameters.AddWithValue("@IsPermanent", permanent);
            cmd.Parameters.AddWithValue("@Photo", photo);
            cmd.Parameters.AddWithValue("@TransType", 2);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void ModifyTempEmployeeInformation(string EmpNo, string fname, string lname, string faName, string dob, string qualification,
        string referedBy, string phoneNo, string personalEmail, string companyEmail, string address, int active, int permanent,string photo)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@EmployeeNo", EmpNo);
            cmd.Parameters.AddWithValue("@EmployeeName", fname + " " + lname);
            cmd.Parameters.AddWithValue("@FatherName", faName);
            cmd.Parameters.AddWithValue("@DOB", dob);
            cmd.Parameters.AddWithValue("@Qualification", qualification);
            cmd.Parameters.AddWithValue("@ReferredBy", referedBy);
            cmd.Parameters.AddWithValue("@PhoneNo", phoneNo);
            cmd.Parameters.AddWithValue("@PersonalEmail", personalEmail);
            cmd.Parameters.AddWithValue("@CompanyEmail", companyEmail);
            cmd.Parameters.AddWithValue("@Address", address);
            cmd.Parameters.AddWithValue("@IsActive", active);
            cmd.Parameters.AddWithValue("@IsPermanent", permanent);
            cmd.Parameters.AddWithValue("@Photo", photo);
            cmd.Parameters.AddWithValue("@TransType", 2);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }


        public static DataTable SearchEmployee(int empType, string empName, string empNo)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IsPermanent", empType);
            cmd.Parameters.AddWithValue("@EmployeeName", empName);
            cmd.Parameters.AddWithValue("@EmployeeNo", empNo);
            cmd.Parameters.AddWithValue("@TransType", 4);
            
            da = new SqlDataAdapter(cmd);
            DataTable  dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;
        }

        public static DataTable SearchEmployeeBasedOnEmpNo(string empNo)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmployeeNo", empNo);
            cmd.Parameters.AddWithValue("@TransType", 3);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;
        }


        public static DataTable GetEmployeeMaxId()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TransType", 5);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;
            
        }

        public  static DataTable getEmployeeIdbasedOnEmployeeNo(string NavigateEmpNo)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmployeeNo", NavigateEmpNo);
            cmd.Parameters.AddWithValue("@TransType", 7);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;   
        }

        public static DataTable getSections()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetSections";
            cmd.Parameters.AddWithValue("@TransType", 2);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable getPermanentEmployeeList()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@TransType", 8);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static void setSectionInformation(string sectionName, string Description, int InchargeID)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetSections";
            cmd.Parameters.AddWithValue("@SectionName", sectionName);
            cmd.Parameters.AddWithValue("@Description", Description);
            cmd.Parameters.AddWithValue("@SectionInchargeId", InchargeID);
            cmd.Parameters.AddWithValue("@TransType", 1);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void updateSectionInformation(int sectionId,string sectionName, string Description, int InchargeID)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetSections";
            cmd.Parameters.AddWithValue("@SectionId", sectionId);
            cmd.Parameters.AddWithValue("@SectionName", sectionName);
            cmd.Parameters.AddWithValue("@Description", Description);
            cmd.Parameters.AddWithValue("@SectionInchargeId", InchargeID);
            cmd.Parameters.AddWithValue("@TransType", 4);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static DataTable getTasks()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetTask";
            cmd.Parameters.AddWithValue("@TransType", 2);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public  static DataTable SectionList()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetSections";
            cmd.Parameters.AddWithValue("@TransType", 3);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static void setTaskInformation(string taskName, string taskDesc, int sectionId, string parameter)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetTask";
            cmd.Parameters.AddWithValue("@TaskName", taskName);
            cmd.Parameters.AddWithValue("@Description", taskDesc);
            cmd.Parameters.AddWithValue("@SectionID", sectionId);
            cmd.Parameters.AddWithValue("@Parameter", parameter);
            cmd.Parameters.AddWithValue("@TransType", 1);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void updateTaskInformation(int taskID,string taskName, string taskDesc, int sectionId, string parameter)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetTask";
            cmd.Parameters.AddWithValue("@TaskID", taskID);
            cmd.Parameters.AddWithValue("@TaskName", taskName);
            cmd.Parameters.AddWithValue("@Description", taskDesc);
            cmd.Parameters.AddWithValue("@SectionID", sectionId);
            cmd.Parameters.AddWithValue("@Parameter", parameter);
            cmd.Parameters.AddWithValue("@TransType", 3);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public  static DataTable getMaxTaskId(int secId)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetTask";
            cmd.Parameters.AddWithValue("@SectionID", secId);
            cmd.Parameters.AddWithValue("@TransType", 4);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;   
        }

        public static DataTable GetEmployeePhoto(string empNo)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmployeeNo", empNo);
            cmd.Parameters.AddWithValue("@TransType", 9);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;
        }

        public static DataTable CompareSectionName(string secName)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetSections";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SectionName", secName);
            cmd.Parameters.AddWithValue("@TransType", 5);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;
            
        }

        public static DataTable CompareTaskName(string taskName)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SP_GetSetTask";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TaskName", taskName);
            cmd.Parameters.AddWithValue("@TransType", 5);

            da = new SqlDataAdapter(cmd);
            DataTable dtEmp = new DataTable();
            da.Fill(dtEmp);
            return dtEmp;

        }

        public  static DataTable getEmployeeListBasedOnEmployeeType(int empType)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@IsPermanent", empType);
            cmd.Parameters.AddWithValue("@TransType", 10);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable getTaskListBasedOnSection(int sectionType)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetTask";
            cmd.Parameters.AddWithValue("@SectionID", sectionType);
            cmd.Parameters.AddWithValue("@TransType", 6);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt; 
        }

        public static DataTable GetEmployeeTaskMapping()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployeeTaskMapping";
            cmd.Parameters.AddWithValue("@TransType", 2);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;    
        }

        public  static DataTable getSectionBasedOnTaskId(string  taskName)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetTask";
            cmd.Parameters.AddWithValue("@TaskName", taskName);
            cmd.Parameters.AddWithValue("@TransType", 7);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;    
        }

        public static DataTable getEmployeeTypeBasedOnId(string empName)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@EmployeeName", empName);
            cmd.Parameters.AddWithValue("@TransType", 11);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;    
        }

        public static void setEmployeeTaskMapping(string taskId, string EmployeeId, string startDt, string endDt, string remarks)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployeeTaskMapping";
            cmd.Parameters.AddWithValue("@TaskId", taskId);
            cmd.Parameters.AddWithValue("@Id", EmployeeId);
            cmd.Parameters.AddWithValue("@StartDate", startDt.Split('/').GetValue(1) + "/" + startDt.Split('/').GetValue(0) + "/" + startDt.Split('/').GetValue(2));
            cmd.Parameters.AddWithValue("@EndDate", endDt.Split('/').GetValue(1) + "/" + endDt.Split('/').GetValue(0) + "/" + endDt.Split('/').GetValue(2));
            cmd.Parameters.AddWithValue("@Remarks", remarks);
            cmd.Parameters.AddWithValue("@TransType", 1);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public  static void updateEmployeeTaskMapping(string Mappingid, string taskId, string EmployeeId, string startDt, string endDt, string remarks)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployeeTaskMapping";
            cmd.Parameters.AddWithValue("@MappingId", Mappingid);
            cmd.Parameters.AddWithValue("@TaskId", taskId);
            cmd.Parameters.AddWithValue("@Id", EmployeeId);
            cmd.Parameters.AddWithValue("@StartDate", startDt.Split('/').GetValue(1) + "/" + startDt.Split('/').GetValue(0) + "/" + startDt.Split('/').GetValue(2));
            cmd.Parameters.AddWithValue("@EndDate", endDt.Split('/').GetValue(1) + "/" + endDt.Split('/').GetValue(0) + "/" + endDt.Split('/').GetValue(2));
            cmd.Parameters.AddWithValue("@Remarks", remarks);
            cmd.Parameters.AddWithValue("@TransType", 3);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static DataTable GetEmployeeAttendenceStatus(int empId)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetAttendence";
            cmd.Parameters.AddWithValue("@Id", empId);
            cmd.Parameters.AddWithValue("@TransType", 3);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;    
        }

        public static void SetEmployeeAttendence(int empId)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetAttendence";
            cmd.Parameters.AddWithValue("@Id", empId);
            cmd.Parameters.AddWithValue("@TransType", 1);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void UpdateEmployeeAttendence(int empId)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetAttendence";
            cmd.Parameters.AddWithValue("@Id", empId);
            cmd.Parameters.AddWithValue("@TransType", 2);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();  
        }

        public static DataTable GetEmployeeAttendenceInformation(int dayDiff)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetAttendenceReport";
            cmd.Parameters.AddWithValue("@DayDiffrence", dayDiff);
            cmd.Parameters.AddWithValue("@TransType", 1);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;    
  
        }

        public static DataTable GetPermanentSectionAttendenceInformation(int dayDiff)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetAttendenceReport";
            cmd.Parameters.AddWithValue("@DayDiffrence", dayDiff);
            cmd.Parameters.AddWithValue("@IsPermanent", 1);
            cmd.Parameters.AddWithValue("@TransType", 2);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;  
        }

        public static DataTable GetTemporarySectionAttendenceInformation(int dayDiff)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetAttendenceReport";
            cmd.Parameters.AddWithValue("@DayDiffrence", dayDiff);
            cmd.Parameters.AddWithValue("@IsPermanent",0);
            cmd.Parameters.AddWithValue("@TransType", 2);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt;  
        }

        public static DataTable GetCountEmployeeTaskMapping(int empId)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployeeTaskMapping";
            cmd.Parameters.AddWithValue("@Id", empId);
            cmd.Parameters.AddWithValue("@TransType", 4);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt; 
        }

        public static DataTable GetEmployeeEnableStatus(int empId)
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSetEmployee";
            cmd.Parameters.AddWithValue("@ID", empId);
            cmd.Parameters.AddWithValue("@TransType", 12);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            return dt; 
            
        }
    }
}
